package com.hc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

/***
 * <p>
 * excel解析工具类(.xls .xlsx)
 * </p>
 * @Date 2016-07-13 16:15:00
 */
@Component
public class ExcelUtil {

	private static final int HEADER = 0;

	private Workbook book;

	private Map<Integer, String> header;

	private SimpleDateFormat format;

	
	/**
	 * 初始化工作簿
	 * 
	 * @param filePath
	 *            文件路径
	 * @param fileType
	 *            文件类型
	 */
	public void init(InputStream inputStream, String fileType) {
		try {
			if (fileType.equals("xls")) {
				book = new HSSFWorkbook(inputStream);
			} else if (fileType.equals("xlsx")) {
				book = new XSSFWorkbook(inputStream);
			} else {
				System.out.println("您输入的excel格式不正确");
			}
		} catch (FileNotFoundException e) {
			LoggerUtil.error(e);
		} catch (IOException e) {
			LoggerUtil.error(e);
		}

	}
	
	/**
	 * 初始化工作簿
	 * 
	 * @param filePath
	 *            文件路径
	 * @param fileType
	 *            文件类型
	 */
	/**
	 * 初始化工作簿
	 *
	 * @param file
	 */
	public void init(String  filePath,String fileType) {
		FileInputStream in;
		try {
			in = new FileInputStream(new File(filePath));
			//book = new HSSFWorkbook(in);
			if (fileType.equals("xls")) {
				book = new HSSFWorkbook(in);
			} else if (fileType.equals("xlsx")) {
				book = new XSSFWorkbook(in);
			} else {
				System.out.println("您输入的excel格式不正确");
			}
		} catch (FileNotFoundException e) {
			LoggerUtil.error(e);
		} catch (IOException e) {
			LoggerUtil.error(e);
		}

	}
	
	/**
	 * 获取第一行标题栏数据
	 * 
	 * @param sheet
	 * @return map key：标题栏列下标（0开始） value 标题栏值
	 */
	private void loadHeader(Sheet sheet) {
		this.header = new HashMap<Integer, String>();
		Row row = sheet.getRow(HEADER);
		int columns = row.getLastCellNum();
		for (int i = 0; i < columns; i++) {
			String value = row.getCell(i).getStringCellValue();
			if (null == value) {
				throw new RuntimeException("标题栏不能为空！");
			}
			header.put(i, value);
		}
	}

	/**
	 * 
	 * @param clazz
	 *            类名
	 * @param required
	 *            是否进行检查
	 * @return List Object 多个对象
	 */
	public <T> List<T> bindToModels(Class<T> clazz, boolean required) throws Exception {
		// 获取第一页
		Sheet sheet = this.book.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();// 获取行数

		if (rowNum < 1) {
			return new ArrayList<T>();
		}
		// 加载标题栏数据
		this.loadHeader(sheet);
		List<T> result = new ArrayList<T>();
		for (int i = 0; i <= rowNum; i++) {
			Row row = sheet.getRow(i);
			if(null == row ||  "".equals(row.getLastCellNum())){
				break;
			}
			int cellNum = row.getLastCellNum();
			T instance = (T) clazz.newInstance();
			for (int columns = 0; columns < cellNum; columns++) {
				Cell cell = row.getCell(columns);
				// 判断单元格的数据类型
				String value = loadCellType(cell);
				if (i <= 1) {
					break;
				}
				// 获取单元格的值
				if (null == value || "".equals(value)) {
					// 如果为必填的则将错误信息记录
					if (required) {
						System.out.println("第" + (i + 1) + "行，" + header.get(columns) + "字段，数据为空，跳过！");
						continue;
					}
				} else {
					String key = header.get(columns);
					// 加载实际值
					this.loadValue(clazz, instance, key, value);
				}
			}
			if (i > 1) {
				result.add(instance);
			}
		}
		return result;
	}

	/**
	 * 将单元格数据转换成string类型
	 * 
	 * @param cellType
	 * @param cell
	 * @return value
	 */
	private String loadCellType(Cell cell) {
		SimpleDateFormat sdf = null;
		String value = null;
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				// 判断当前的cell是否为Date
				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm:ss")) {
					sdf = new SimpleDateFormat("HH:mm:ss");
					Date date = cell.getDateCellValue();
					value = sdf.format(date);
				} else {
					value = String.valueOf((long) cell.getNumericCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				break;
			}
		}
		return value;
	}

	/**
	 * 注入属性值
	 * 
	 * @param instance
	 * @param pro
	 *            属性对象
	 * @param value
	 *            属性值
	 */
	private <T> void loadValue(Class<T> clazz, T instance, String pro, String value)
			throws SecurityException, NoSuchMethodException, Exception {
		String getMethod = this.initGetMethod(pro);
		@SuppressWarnings("rawtypes")
		Class type = clazz.getDeclaredMethod(getMethod, null).getReturnType();

		Method method = clazz.getMethod(this.initSetMethod(pro), type);

		if (type == String.class) {
			method.invoke(instance, value);
		} else if (type == int.class || type == Integer.class) {
			method.invoke(instance, Integer.parseInt(value));

		} else if (type == long.class || type == Long.class) {
			method.invoke(instance, Long.parseLong(value));

		} else if (type == float.class || type == Float.class) {
			method.invoke(instance, Float.parseFloat(value));

		} else if (type == double.class || type == Double.class) {
			method.invoke(instance, Double.parseDouble(value));       

		} else if (type == Date.class) {
			method.invoke(instance, this.parseDate(value));
		}

	}

	private Date parseDate(String value) throws ParseException {
		return format.parse(value);
	}

	@SuppressWarnings("unused")
	private String formateDate(Date date) {
		return format.format(date);
	}

	public String initSetMethod(String field) {
		return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	public String initGetMethod(String field) {
		return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

}
